package com.study.samsung.Dao;

import cn.hutool.core.util.StrUtil;
import com.study.samsung.Entity.Commodity;
import com.study.samsung.Utils.Pagers;
import com.study.samsung.Utils.RowMapper;
import com.study.samsung.Utils.SQLHelper;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class CommodityDAO {
    private SQLHelper helper = new SQLHelper();

//    public Integer totalPageCount(){
//        String sql = "select count(*) from commodity";
//        return helper.one(sql,rs -> {
//            return rs.getInt(1);
//        });
//    }
//
//    public List<Commodity> page(int begin,int pageSize){
//        String sql = "select * from commodity a left join classification b on a.lid = b.lid " +
//                "left join succession c on b.xid = c.xid limit ?,?";
//        return helper.query(sql,rs -> new Commodity(rs.getInt("sid"),
//                rs.getString("sname"),
//                rs.getString("url"),
//                rs.getBigDecimal("sprice"),
//                rs.getBoolean("isdefault"),
//                rs.getInt("lid"),
//                rs.getString("lname")),begin,pageSize);
//    }



    public Pagers htselectAll(Integer pageNo, Integer pageSize, String inputvalue){
        String sql1 = "select count(*) from commodity a left join classification b " +
                "on a.lid = b.lid left join succession c on b.xid = c.xid where 1=1 ";

        String sql2 = "select * from commodity a left join classification b " +
                "on a.lid = b.lid left join succession c on b.xid = c.xid where 1=1 ";

        ArrayList<Object> params = new ArrayList<>();
        StringBuilder stringBuilder = new StringBuilder();
        if (!StrUtil.isEmpty(inputvalue)){
            stringBuilder.append(" and a.sname like concat('%',?,'%')");
            params.add(inputvalue);
        }
        Integer total = helper.one(sql1 + stringBuilder.toString(), rs -> {
            return rs.getInt(1);
        }, params.toArray());

        stringBuilder.append(" limit ?,?");
        params.add((pageNo-1)*pageSize);
        params.add(pageSize);

        List<Commodity> query = helper.query(sql2 + stringBuilder.toString(), rs -> {
            return new Commodity(rs.getInt("sid"), rs.getString("sname"), rs.getString("url"),
                    rs.getBigDecimal("sprice"), rs.getBoolean("isdefault"),
                    rs.getInt("lid"), rs.getString("lname"));
        }, params.toArray());
        System.out.println(total);
        System.out.println(query);
        Pagers pagers = new Pagers(pageNo, pageSize);
        System.out.println(pagers.getPageNo());
        pagers.setTotal(total);
        pagers.setData(query);
        return pagers;
    }


    public List<Commodity> selectAll(){
        String sql = "select * from commodity";
        List<Commodity> query = helper.query(sql, new CommodityRowMapper());
        return query;
    }
    public Integer changeDown(Integer sid){
        String sql = "update commodity set isdefault = ? where sid = ?";
        return helper.update(sql,false,sid);
    }

    public Integer changeUp(Integer sid){
        String sql = "update commodity set isdefault = ? where sid = ?";
        return helper.update(sql,true,sid);
    }

    public Commodity selectByid(Integer sid){
        String sql = "select * from commodity where sid = ?";
        return helper.one(sql,new CommodityRowMapper(),sid);
    }

    public Integer delgoods(Integer sid){
        String sql = "delete from commodity where sid = ?";
        return helper.update(sql,sid);
    }

    public List<Commodity> htselectByid(Integer sid){
        String sql = "select * from commodity a left join classification b on a.lid = b.lid " +
                "left join succession c on b.xid = c.xid" +
                " where sid = ?";
        return helper.query(sql,rs -> new Commodity(rs.getInt("sid"),
                rs.getString("sname"),
                rs.getString("url"),
                rs.getBigDecimal("sprice"),
                rs.getBoolean("isdefault"),
                rs.getInt("lid"),
                rs.getString("lname")),sid);
    }

    public Integer Update(Commodity commodity){
        String sql = "update commodity set sname = ?,url = ?,sprice = ?,isdefault = ?,lid = ? where sid = ?";
        return helper.update(sql,commodity.getSname(),commodity.getUrl(),commodity.getSprice(),
                commodity.getIsdefault(),commodity.getLid(),commodity.getSid());
    }

    public Integer addgoods(Commodity commodity){
        String sql = "insert into commodity values(?,?,?,?,?,?)";
        return helper.insert(sql,commodity.getSid(),commodity.getSname(),commodity.getUrl(),commodity.getSprice(),
                commodity.getIsdefault(),commodity.getLid());
    }


    class CommodityRowMapper implements RowMapper<Commodity>{

        @Override
        public Commodity map(ResultSet rs) throws SQLException {
            return new Commodity(rs.getInt("sid"),
                                 rs.getString("sname"),
                                 rs.getString("url"),
                                 rs.getBigDecimal("sprice"),
                                 rs.getBoolean("isdefault"),
                                 rs.getInt("lid"));
        }
    }



}
